﻿using KINLO.Model;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using KINLO.Model.Dto;
using System.Configuration;

namespace KINLO.Data.Reporitory
{
    public class WeightReporitory
    {
        public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["WeightDB"].ToString();
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(typeof(WeightReporitory));
        Database DB;
        public WeightReporitory()
        {
            DB = (new DbContext("name=WeightDB")).Database;
        }
        /// <summary>
        ///  根据电池条码查询
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public Weight GetBattryByBarCode(string code)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    log.Info(string.Format("根据电池条码查询 {0}", code));
                    var cmdTxt = @"SELECT * FROM dbo.Weight WHERE BattBarCode=@BattBarCode";
                    var cmd = new SqlCommand(cmdTxt, conn);
                    cmd.Parameters.Add(
                    new SqlParameter("@BattBarCode", code));
                    conn.Open();
                    var reader = cmd.ExecuteReader();
                    bool success = false;
                    var model = reader.ReaderToModel<Weight>(ref success);
                    return success ? model : null; 
                }

                //log.Info(string.Format("根据电池条码查询 {0}", code)); 
                //var cmd = @"SELECT * FROM dbo.Weight WHERE BattBarCode=@BattBarCode";
                //return DB.SqlQuery<Weight>(cmd,
                //    new SqlParameter("@BattBarCode", code)
                //    ).ToList().FirstOrDefault();

            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "GetBattryByBarCode", ex));
                return null;
            }
        }

        /// <summary>
        ///  根据托架号查询
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public Weight GetBattryByTrayCode(string code)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    log.Info(string.Format("根据托架号查询 {0}", code));
                    var cmdTxt = @"SELECT TOP 1 * FROM dbo.Weight WHERE TrayCode=@TrayCode and Finished is null ORDER BY ProductDate DESC ";
                    var cmd = new SqlCommand(cmdTxt, conn);
                    cmd.Parameters.Add( new SqlParameter("@TrayCode", code));
                    conn.Open(); 
                    var reader = cmd.ExecuteReader();
                    bool success = false;
                    var model = reader.ReaderToModel<Weight>(ref success);
                    return success ? model : null;
                } 
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "GetBattryByTrayCode", ex));
                return null;
            }
        }

        /// <summary>
        ///  根据托架号和行号查询
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<Weight> GetBattryByTrayCodeTrayRow(short tray,short row)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    var time = DateTime.Now.AddDays(-2);
                    log.Info(string.Format("根据托架号查询 {0},行号:{1}", tray, row));
                    var cmdTxt = string.Format($"SELECT  * FROM dbo.Weight WHERE TrayCode='{tray}' AND RowNo in('{row}','{row+2}')  and FWeightTime>'{time}'");
                    var cmd = new SqlCommand(cmdTxt, conn); 
                    conn.Open();
                    var reader = cmd.ExecuteReader();
                    return reader.ReaderToList<Weight>();
                } 
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "GetBattryByTrayCode", ex));
                return null;
            }
        }

        /// <summary>
        ///  补液根据托架号查询
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public Weight GetBattryByTrayCodeInRepair(string code)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    log.Info(string.Format("根据托架号查询 {0}", code));
                    var cmdTxt = @"SELECT TOP 1 * FROM dbo.Weight WHERE TrayCode=@TrayCode and Finished is not null and RepairFinished=0  ORDER BY ProductDate DESC ";
                    var cmd = new SqlCommand(cmdTxt, conn);
                    cmd.Parameters.Add(new SqlParameter("@TrayCode", code));
                    conn.Open();
                    var reader = cmd.ExecuteReader();
                    bool success = false;
                    var model = reader.ReaderToModel<Weight>(ref success);
                    return success ? model : null;
                } 
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "GetBattryByTrayCode", ex));
                return null;
            }
        }

        /// <summary>
        ///  根据ID查询
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public Weight GetBattryByID(Guid guid)
        {
            try
            {

                using (var conn = new SqlConnection(ConnectionString))
                {

                    log.Info(string.Format("根据ID查询 {0}", guid));
                    var cmdTxt = @"SELECT * FROM dbo.Weight WHERE ID=@guid";
                    var cmd = new SqlCommand(cmdTxt, conn);
                    cmd.Parameters.Add(new SqlParameter("@ID", guid));
                    conn.Open();
                    var reader = cmd.ExecuteReader();
                    bool success = false;
                    var model = reader.ReaderToModel<Weight>(ref success);
                    return success ? model : null;
                } 
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "GetBattryByTrayCode", ex));
                return null;
            }
        }
         
         
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="weight"></param>
        /// <returns></returns>
        public bool UpdateBattryInfo(Weight w)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    log.Info("修改电池信息");
                    var cmdTxt = @"	UPDATE dbo.Weight
	                SET [Index]=@Index, [LineNo]=@LineNo,Stage=@Stage,TrayCode=@TrayCode,BattBarCode=@BattBarCode,
	                FInjectionWeight=@FInjectionWeight,BInjectionWeight=@BInjectionWeight,InjectionWeight=@InjectionWeight,
	                IsGoods=@IsGoods,DevValue=@DevValue,Range=@Range,FScanTime=@FScanTime,FWeightTime=@FWeightTime,
	                BWeightTime=@BWeightTime,RepairInjectionWeight=@RepairInjectionWeight,ProductDate=@ProductDate,
	                RepairInjectionWeightTime=@RepairInjectionWeightTime,BRepairInjectionWeight=@BRepairInjectionWeight,
	                TheoryRepairValue=@TheoryRepairValue,ActualRepairValue=@ActualRepairValue,Finished=@Finished,DevValue2=@DevValue2,
                    BScanTime=@BScanTime,RepairFinished=@RepairFinished,Range2=@Range2,RowNo=@RowNo,Flag=@Flag ,StdInj=@StdInj
                    ,InjLow=@InjLow ,InjUp=@InjUp,StationNo=@StationNo,OrderNo=@OrderNo,OrderNo2=@OrderNo2,LeakHuntingResult=@LeakHuntingResult  Where ID=@ID";
                    var cmd = new SqlCommand(cmdTxt, conn);  
                    cmd.Parameters.Add(new SqlParameter("@ID", w.ID));
                    cmd.Parameters.Add(new SqlParameter("@Index", w.Index == null ? DBNull.Value : (object)w.Index));
                    cmd.Parameters.Add(new SqlParameter("@RowNo", w.RowNo == null ? DBNull.Value : (object)w.RowNo));
                    cmd.Parameters.Add(new SqlParameter("@LineNo", w.LineNo == null ? DBNull.Value : (object)w.LineNo));
                    cmd.Parameters.Add(new SqlParameter("@Stage", w.Stage == null ? DBNull.Value : (object)w.Stage));
                    cmd.Parameters.Add(new SqlParameter("@TrayCode", w.TrayCode == null ? DBNull.Value : (object)w.TrayCode));
                    cmd.Parameters.Add(new SqlParameter("@BattBarCode", w.BattBarCode == null ? DBNull.Value : (object)w.BattBarCode));
                    cmd.Parameters.Add(new SqlParameter("@FInjectionWeight", w.FInjectionWeight == null ? DBNull.Value : (object)w.FInjectionWeight));
                    cmd.Parameters.Add(new SqlParameter("@BInjectionWeight", w.BInjectionWeight == null ? DBNull.Value : (object)w.BInjectionWeight));
                    cmd.Parameters.Add(new SqlParameter("@InjectionWeight", w.InjectionWeight == null ? DBNull.Value : (object)w.InjectionWeight));
                    cmd.Parameters.Add(new SqlParameter("@IsGoods", w.IsGoods == null ? DBNull.Value : (object)w.IsGoods));
                    cmd.Parameters.Add(new SqlParameter("@DevValue", w.DevValue == null ? DBNull.Value : (object)w.DevValue));
                    cmd.Parameters.Add(new SqlParameter("@Range", w.Range == null ? DBNull.Value : (object)w.Range));
                    cmd.Parameters.Add(new SqlParameter("@Range2", w.Range == null ? DBNull.Value : (object)w.Range2));
                    cmd.Parameters.Add(new SqlParameter("@FScanTime", w.FScanTime == null ? DBNull.Value : (object)w.FScanTime));
                    cmd.Parameters.Add(new SqlParameter("@FWeightTime", w.FWeightTime == null ? DBNull.Value : (object)w.FWeightTime));
                    cmd.Parameters.Add(new SqlParameter("@BWeightTime", w.BWeightTime == null ? DBNull.Value : (object)w.BWeightTime));
                    cmd.Parameters.Add(new SqlParameter("@RepairInjectionWeight", w.RepairInjectionWeight == null ? DBNull.Value : (object)w.RepairInjectionWeight));
                    cmd.Parameters.Add(new SqlParameter("@RepairInjectionWeightTime", w.RepairInjectionWeightTime == null ? DBNull.Value : (object)w.RepairInjectionWeightTime));
                    cmd.Parameters.Add(new SqlParameter("@BRepairInjectionWeight", w.BRepairInjectionWeight == null ? DBNull.Value : (object)w.BRepairInjectionWeight));
                    cmd.Parameters.Add(new SqlParameter("@TheoryRepairValue", w.TheoryRepairValue == null ? DBNull.Value : (object)w.TheoryRepairValue));
                    cmd.Parameters.Add(new SqlParameter("@ActualRepairValue", w.ActualRepairValue == null ? DBNull.Value : (object)w.ActualRepairValue));
                    cmd.Parameters.Add(new SqlParameter("@Finished", w.Finished == null ? DBNull.Value : (object)w.Finished));
                    cmd.Parameters.Add(new SqlParameter("@RepairFinished", w.RepairFinished == null ? DBNull.Value : (object)w.RepairFinished));
                    cmd.Parameters.Add(new SqlParameter("@ProductDate", w.ProductDate == null ? DBNull.Value : (object)w.ProductDate));
                    cmd.Parameters.Add(new SqlParameter("@BScanTime", w.BScanTime == null ? DBNull.Value : (object)w.BScanTime));
                    cmd.Parameters.Add(new SqlParameter("@DevValue2", w.DevValue2 == null ? DBNull.Value : (object)w.DevValue2));
                    cmd.Parameters.Add(new SqlParameter("@Flag", w.Flag == null ? DBNull.Value : (object)w.Flag));
                    cmd.Parameters.Add(new SqlParameter("@StdInj", w.StdInj == null ? DBNull.Value : (object)w.StdInj));
                    cmd.Parameters.Add(new SqlParameter("@InjLow", w.InjLow == null ? DBNull.Value : (object)w.InjLow));
                    cmd.Parameters.Add(new SqlParameter("@InjUp", w.InjUp == null ? DBNull.Value : (object)w.InjUp));
                    cmd.Parameters.Add(new SqlParameter("@StationNo", w.StationNo == null ? DBNull.Value : (object)w.StationNo));
                    cmd.Parameters.Add(new SqlParameter("@OrderNo", w.OrderNo == null ? DBNull.Value : (object)w.OrderNo));
                    cmd.Parameters.Add(new SqlParameter("@OrderNo2", w.OrderNo2 == null ? DBNull.Value : (object)w.OrderNo2));
                    cmd.Parameters.Add(new SqlParameter("@LeakHuntingResult", w.LeakHuntingResult == null ? DBNull.Value : (object)w.LeakHuntingResult));
                    conn.Open();
                    var result = cmd.ExecuteNonQuery();
                    return result > 0 ? true : false;
                } 
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "UpdateBattryInfo", ex));
                return false;
            }

        }

        /// <summary>
        /// 新增电池信息
        /// </summary>
        /// <param name="w"></param>
        /// <returns></returns>
        public bool AddBattryInfo(Weight w)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    log.Info("新增电池信息");
                    var cmdTxt = @"INSERT dbo.Weight (ID,[Index], [LineNo],Stage,TrayCode,BattBarCode,
                         FInjectionWeight,BInjectionWeight,InjectionWeight,
                         IsGoods,DevValue,Range,FScanTime,FWeightTime,
                         BWeightTime,RepairInjectionWeight,
                         RepairInjectionWeightTime,BRepairInjectionWeight,
                         TheoryRepairValue,ActualRepairValue,Finished,DevValue2,ProductDate,BScanTime,RepairFinished,Range2,
                        RowNo ,Flag ,StdInj ,InjLow ,InjUp,StationNo,OrderNo,OrderNo2,LeakHuntingResult)
                      VALUES  ( @ID,@Index,@LineNo,@Stage,@TrayCode,@BattBarCode,
                         @FInjectionWeight,@BInjectionWeight,@InjectionWeight,
                         @IsGoods,@DevValue,@Range,@FScanTime,@FWeightTime,
                         @BWeightTime,@RepairInjectionWeight,
                         @RepairInjectionWeightTime,@BRepairInjectionWeight,
                         @TheoryRepairValue,@ActualRepairValue,@Finished,@DevValue2,@ProductDate,@BScanTime,@RepairFinished,
                        @Range2,@RowNo,@Flag ,@StdInj ,@InjLow ,@InjUp,@StationNo,@OrderNo,@OrderNo2,@LeakHuntingResult)";
                    var cmd = new SqlCommand(cmdTxt, conn);
                    cmd.Parameters.Add(new SqlParameter("@ID", w.ID));
                    cmd.Parameters.Add(new SqlParameter("@Index", w.Index == null ? DBNull.Value : (object)w.Index));
                    cmd.Parameters.Add(new SqlParameter("@RowNo", w.RowNo == null ? DBNull.Value : (object)w.RowNo));
                    cmd.Parameters.Add(new SqlParameter("@LineNo", w.LineNo == null ? DBNull.Value : (object)w.LineNo));
                    cmd.Parameters.Add(new SqlParameter("@Stage", w.Stage == null ? DBNull.Value : (object)w.Stage));
                    cmd.Parameters.Add(new SqlParameter("@TrayCode", w.TrayCode == null ? DBNull.Value : (object)w.TrayCode));
                    cmd.Parameters.Add(new SqlParameter("@BattBarCode", w.BattBarCode == null ? DBNull.Value : (object)w.BattBarCode));
                    cmd.Parameters.Add(new SqlParameter("@FInjectionWeight", w.FInjectionWeight == null ? DBNull.Value : (object)w.FInjectionWeight));
                    cmd.Parameters.Add(new SqlParameter("@BInjectionWeight", w.BInjectionWeight == null ? DBNull.Value : (object)w.BInjectionWeight));
                    cmd.Parameters.Add(new SqlParameter("@InjectionWeight", w.InjectionWeight == null ? DBNull.Value : (object)w.InjectionWeight));
                    cmd.Parameters.Add(new SqlParameter("@IsGoods", w.IsGoods == null ? DBNull.Value : (object)w.IsGoods));
                    cmd.Parameters.Add(new SqlParameter("@DevValue", w.DevValue == null ? DBNull.Value : (object)w.DevValue));
                    cmd.Parameters.Add(new SqlParameter("@Range", w.Range == null ? DBNull.Value : (object)w.Range));
                    cmd.Parameters.Add(new SqlParameter("@Range2", w.Range == null ? DBNull.Value : (object)w.Range2));
                    cmd.Parameters.Add(new SqlParameter("@FScanTime", w.FScanTime == null ? DBNull.Value : (object)w.FScanTime));
                    cmd.Parameters.Add(new SqlParameter("@FWeightTime", w.FWeightTime == null ? DBNull.Value : (object)w.FWeightTime));
                    cmd.Parameters.Add(new SqlParameter("@BWeightTime", w.BWeightTime == null ? DBNull.Value : (object)w.BWeightTime));
                    cmd.Parameters.Add(new SqlParameter("@RepairInjectionWeight", w.RepairInjectionWeight == null ? DBNull.Value : (object)w.RepairInjectionWeight));
                    cmd.Parameters.Add(new SqlParameter("@RepairInjectionWeightTime", w.RepairInjectionWeightTime == null ? DBNull.Value : (object)w.RepairInjectionWeightTime));
                    cmd.Parameters.Add(new SqlParameter("@BRepairInjectionWeight", w.BRepairInjectionWeight == null ? DBNull.Value : (object)w.BRepairInjectionWeight));
                    cmd.Parameters.Add(new SqlParameter("@TheoryRepairValue", w.TheoryRepairValue == null ? DBNull.Value : (object)w.TheoryRepairValue));
                    cmd.Parameters.Add(new SqlParameter("@ActualRepairValue", w.ActualRepairValue == null ? DBNull.Value : (object)w.ActualRepairValue));
                    cmd.Parameters.Add(new SqlParameter("@Finished", w.Finished == null ? DBNull.Value : (object)w.Finished));
                    cmd.Parameters.Add(new SqlParameter("@RepairFinished", w.RepairFinished == null ? DBNull.Value : (object)w.RepairFinished));
                    cmd.Parameters.Add(new SqlParameter("@ProductDate", w.ProductDate == null ? DBNull.Value : (object)w.ProductDate));
                    cmd.Parameters.Add(new SqlParameter("@BScanTime", w.BScanTime == null ? DBNull.Value : (object)w.BScanTime));
                    cmd.Parameters.Add(new SqlParameter("@DevValue2", w.DevValue2 == null ? DBNull.Value : (object)w.DevValue2));
                    cmd.Parameters.Add(new SqlParameter("@Flag", w.Flag == null ? DBNull.Value : (object)w.Flag));
                    cmd.Parameters.Add(new SqlParameter("@StdInj", w.StdInj == null ? DBNull.Value : (object)w.StdInj));
                    cmd.Parameters.Add(new SqlParameter("@InjLow", w.InjLow == null ? DBNull.Value : (object)w.InjLow));
                    cmd.Parameters.Add(new SqlParameter("@InjUp", w.InjUp == null ? DBNull.Value : (object)w.InjUp));
                    cmd.Parameters.Add(new SqlParameter("@StationNo", w.StationNo == null ? DBNull.Value : (object)w.StationNo));
                    cmd.Parameters.Add(new SqlParameter("@OrderNo", w.OrderNo == null ? DBNull.Value : (object)w.OrderNo));
                    cmd.Parameters.Add(new SqlParameter("@OrderNo2", w.OrderNo2 == null ? DBNull.Value : (object)w.OrderNo2));
                    cmd.Parameters.Add(new SqlParameter("@LeakHuntingResult", w.LeakHuntingResult == null ? DBNull.Value : (object)w.LeakHuntingResult));
                    conn.Open();
                    var result = cmd.ExecuteNonQuery();
                    log.Info(string.Format("新增电池信息 托架号 TrayCode:{0}", w.TrayCode));
                    return result > 0 ? true : false;
                }

            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "AddBattryInfo", ex));
                return false;
            } 
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public bool DeleteBattryInfo(Guid guid)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    log.Info("删除称重信息");
                    var cmdTxt = @"DELETE dbo.Weight WHERE ID=@ID";
                    var cmd = new SqlCommand(cmdTxt, conn);
                    cmd.Parameters.Add(new SqlParameter("@ID", guid)); 
                    conn.Open();
                    var result = cmd.ExecuteNonQuery(); 
                    return result > 0 ? true : false;
                }                 
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "DeleteBattryInfo", ex));
                return false;
            }
        }

        /// <summary>
        /// 根据时间段查电池信息
        /// </summary>
        /// <param name="stage"></param>
        /// <returns></returns>
        public List<Weight> GetBattryInfoByTimeSegment (DateTime startTime,DateTime endTime)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    var cmdTxt = @"SELECT *  FROM dbo.Weight WHERE FScanTime BETWEEN @startTime AND @endTime  ORDER BY FScanTime desc ";
                    var cmd = new SqlCommand(cmdTxt, conn);
                    cmd.Parameters.Add(
                    new SqlParameter("@startTime", startTime));
                    cmd.Parameters.Add(new SqlParameter("@endTime", endTime));
                    conn.Open();
                    var reader = cmd.ExecuteReader();
                    return reader.ReaderToList<Weight>();
                }              
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "GetBattryInfoByTimeSegment", ex));
                return null;
            } 
        }

        /// <summary>
        /// 前段未完成数据1
        /// </summary>
        /// <returns></returns>
        public List<Weight> GetBattryInfoByFrontSegment()
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    var cmdTxt = @"SELECT  * FROM dbo.Weight WHERE Stage<3 ORDER BY TrayCode DESC,RowNo DESC,[Index] ";
                    var cmd = new SqlCommand(cmdTxt, conn); 
                    conn.Open();
                    var reader = cmd.ExecuteReader();
                    return reader.ReaderToList<Weight>();
                } 
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "GetBattryInfoByFrontSegment", ex));
                return null;
            }
        }
        /// <summary>
        /// 前段未完成数据2
        /// </summary>
        /// <returns></returns>
        public List<WeightDto> GetBattryInfoByFrontSegment2()
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    var cmdTxt = @"SELECT * FROM dbo.Weight WHERE Stage<3 ORDER BY TrayCode DESC,RowNo DESC,[Index] ";
                    var cmd = new SqlCommand(cmdTxt, conn);
                    conn.Open();
                    var reader = cmd.ExecuteReader();
                    return reader.ReaderToList<WeightDto>();
                } 
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "GetBattryInfoByFrontSegment2", ex));
                return null;
            }
        }

        public List<Weight> GetBattryInfoByStage()
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    var time = DateTime.Now.AddDays(-1);
                    var cmdTxt = string.Format(@"SELECT  *  FROM dbo.Weight WHERE Stage<3 and FWeightTime>'{0}' ORDER BY TrayCode DESC,RowNo DESC,[Index] ", time);
                    var cmd = new SqlCommand(cmdTxt, conn);
                    conn.Open();
                    var reader = cmd.ExecuteReader();
                    return reader.ReaderToList<Weight>();
                } 
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "GetBattryInfoByStage", ex));
                return null;
            }
        }

        /// <summary>
        /// 电池信息查询 报表
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="limit"></param>
        /// <param name="batch"></param>
        /// <param name="numb"></param>
        /// <param name="isGoods"></param>
        /// <returns></returns>
        public List<WeightDto> GetBattryInfo(DateTime start,DateTime end,bool limit,string batch,string numb,string isGoods)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    var cmdTxt = string.Format(@"SELECT ID,[Index] ,[LineNo] ,Stage ,TrayCode ,BattBarCode ,FInjectionWeight ,BInjectionWeight ,InjectionWeight ,
                           IsGoods ,DevValue ,DevValue2 ,Range ,FScanTime ,FWeightTime ,BWeightTime ,RepairInjectionWeight ,BScanTime,
                           RepairInjectionWeightTime ,BRepairInjectionWeight ,TheoryRepairValue ,ActualRepairValue,ProductDate ,Finished ,RepairFinished,
                           (SELECT  TOP 1 WeightRef1  FROM dbo.ParaConfig) WeightRef,(SELECT  TOP 1 WeightWave1  FROM dbo.ParaConfig) WeightWave,Range2,
                           RowNo,StationNo,LeakHuntingResult FROM  dbo.Weight WHERE   (ProductDate BETWEEN @StartTime AND @EndTime OR 1=@NotLimitDate) 
                         AND [LineNo] LIKE @PrdBatch AND BattBarCode LIKE @Number AND IsGoods in ({0})
                         ORDER BY ProductDate DESC", isGoods);
                    var cmd = new SqlCommand(cmdTxt, conn);

                    cmd.Parameters.Add(new SqlParameter("@StartTime", start));
                    cmd.Parameters.Add(new SqlParameter("@EndTime", end));
                    cmd.Parameters.Add(new SqlParameter("@NotLimitDate", limit));
                    cmd.Parameters.Add(new SqlParameter("@PrdBatch", batch));
                    cmd.Parameters.Add(new SqlParameter("@Number", numb));
                    conn.Open();
                    var reader = cmd.ExecuteReader();
                    return reader.ReaderToList<WeightDto>();
                }

            }
            catch(Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "GetBattryInfo", ex));
                return null;
            } 
        }

        /// <summary>
        /// 导前称重信息
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<Weight> GetFrontWeightExportToExcel(DateTime startTime, DateTime endTime)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    var cmdTxt = @"SELECT * FROM dbo.Weight  WHERE FWeightTime BETWEEN @startTime AND @endTime";
                    var cmd = new SqlCommand(cmdTxt, conn);

                    cmd.Parameters.Add(new SqlParameter("@startTime", startTime));
                    cmd.Parameters.Add(new SqlParameter("@endTime", endTime)); 
                    conn.Open();
                    var reader = cmd.ExecuteReader();
                    return reader.ReaderToList<Weight>();
                }

            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "GetFrontWeightExportToExcel", ex));
                return null;
            } 
        }

        /// <summary>
        /// 导后称重信息
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<Weight> GetAfterWeightExportToExcel(DateTime startTime, DateTime endTime)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    var cmdTxt = @"SELECT  *  FROM dbo.Weight
                           WHERE BWeightTime BETWEEN @startTime AND @endTime";
                    var cmd = new SqlCommand(cmdTxt, conn);

                    cmd.Parameters.Add(new SqlParameter("@startTime", startTime));
                    cmd.Parameters.Add(new SqlParameter("@endTime", endTime));
                    conn.Open();
                    var reader = cmd.ExecuteReader();
                    return reader.ReaderToList<Weight>();
                }

            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "GetFrontWeightExportToExcel", ex));
                return null;
            } 
        }

        public int DeleteWeightRecordByDays(int day)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {

                    var cmdTxt = @"EXEC dbo.DeleteWeightRecordByDays @day";
                    var cmd = new SqlCommand(cmdTxt, conn);
                   // cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(  new SqlParameter("@day", day)); 
                    conn.Open();
                    var result = cmd.ExecuteNonQuery();
                    log.Info(string.Format("删除 {0}天前的记录", day));
                    return result;
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "DeleteWeightRecordByDays", ex));
                return 0;
            }

            //var cmd = @"EXEC dbo.DeleteWeightRecordByDays @day";
            //return DB.ExecuteSqlCommand(cmd,
            //    new SqlParameter("@day", day));
        }

        /// <summary>
        /// 前扫码更新过去相同托架状态
        /// </summary>
        /// <param name="trayNo"></param>
        /// <returns></returns>
        public int UpdateTrayNoStatus(string trayNo,string rowNo)
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    var cmdTxt = $"UPDATE dbo.Weight SET Flag=1 WHERE TrayCode='{trayNo}' AND RowNo in('{rowNo}','{rowNo+2}')";
                    var cmd = new SqlCommand(cmdTxt, conn); 
                    conn.Open();
                    var result = cmd.ExecuteNonQuery(); 
                    return result;
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "DeleteWeightRecordByDays", ex));
                return 0;
            } 
        }

        public int UpdateTrayNoStatus(List<ushort> trays)
        {
            try
            {
                if (trays.Count > 0)
                {
                    var traysStr = string.Join(",", trays.Select(s => $"'{s}'")).TrimEnd(',');
                    using (var conn = new SqlConnection(ConnectionString))
                    {
                        var cmdTxt = $"UPDATE dbo.Weight SET Flag=1 WHERE TrayCode in({traysStr})";
                        var cmd = new SqlCommand(cmdTxt, conn);
                        conn.Open();
                        var result = cmd.ExecuteNonQuery();
                        return result;
                    }
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("class:{0},method:{1},发生了错误:{2}", "WeightReporitory", "DeleteWeightRecordByDays", ex));
                return 0;
            }
        }

    }
}
